import cx_Oracle # Descargar libreria -> http://cx-oracle.sourceforge.net/
import sys

class BD():    
    db_conn = ""
    base=""
    ''' 
    Metodo para conectarse a la BD
    '''
    def  __init__(self):
        self.conectarBD()
        
    def conectarBD(self):
        conn_str = 'user/password@127.0.0.1:1521/xe'
        self.db_conn = cx_Oracle.connect(conn_str)
    '''
    Metodo para desconectarse de la BD
    '''
    def desconectarBD(self):
        self.db_conn.close()
    '''
    Realizar consultas SQL
    $1 - consulta en lenguaje SQL a ejecutar en la BD 
    '''
    def consultarBD(self, consulta):
        cursor = self.db_conn.cursor()
        try:
            cursor.execute(consulta)
            salida = cursor.fetchall()
        except:  
            print "Error al realizar la consulta, la consuta fue:"
            print "               " + consulta
            sys.exit()
        return salida
    
    def getTablePKs(self,tabla):
        tabla = tabla.upper()
        resultado_pre = self.consultarBD("SELECT column_name FROM all_cons_columns WHERE table_name = '"+tabla+"' and substr(constraint_name,-3)='_PK' order by position") 
        resultado = []
        for i in resultado_pre:
            aux= str(i).split("'")
            resultado.append(aux[1])
        return resultado
    
    def getTableColumns(self, tabla):
        tabla = tabla.upper()
        return self.consultarBD("SELECT column_name FROM all_tab_columns WHERE table_name = '"+tabla+"'")
    
    def existe_pk_en_tabla(self, pk, tabla):
        tabla = tabla.upper()
        pk = pk.upper()
        listaPks= self.getTablePKs(tabla)
        for r in listaPks:
            if str(r) == "('"+pk+"',)":
                return True
        return False
    
    def tableExists(self,tabla):
        tabla = tabla.upper()
        resultado = self.consultarBD("SELECT table_name FROM all_tables WHERE table_name = '"+tabla+"'")
        return resultado.__len__()>0    
    
    def getColumnType(self, campo, tabla):
        campo = campo.upper()
        tabla = tabla.upper()
        resultado = self.consultarBD("select data_type from all_tab_columns where table_name='"+tabla+"' and column_name='"+campo+"'")
        if len(resultado) == 0:
            return None
        else:
            aux= str(resultado).split("'")
            return aux[1]
    
    def getFkTable(self, tabla , fk):
        tabla = tabla.upper()
        fk = fk.upper()
        resultado_pre = self.consultarBD("select DISTINCT(table_name) "+
                                         "from all_cons_columns where constraint_name "+
                                         "in (select r_constraint_name "+
                                              "from all_constraints "+
                                              "where constraint_type = 'R' "+
                                                "and table_name = '"+tabla+"'"+
                                                "and constraint_name in (select constraint_name "+
                                                                         "from all_cons_columns "+
                                                                         "where table_name = '"+tabla+"' "+
                                                                         "and column_name='"+fk+"'))")
        resultado = []
        for i in resultado_pre:
            aux= str(i).split("'")
            resultado.append(aux[1])
        return resultado
    # Retorna las fk y pk de un JOIN (ordenadas [fk1,pk1,fk2,pk2]
    
    def existe_campo_en_tabla(self, campo, tablas):
        campo = campo.upper()
        for tabla in tablas:
            tabla = tabla.upper()
            listaCampos= self.getTableColumns(tabla)
            for r in listaCampos:
                if str(r) == "('"+campo+"',)":
                    return True
        return False
    
    def campos_join(self, tabla1, tabla2):
        tabla1 = tabla1.upper()
        tabla2 = tabla2.upper()
        resultado_pre = self.consultarBD("SELECT column_name FROM all_tab_columns WHERE table_name = '"+tabla1+"' INTERSECT SELECT column_name FROM all_tab_columns WHERE table_name = '"+tabla2+"'")
        resultado = []
        for i in resultado_pre:
            aux= str(i).split("'")
            resultado.append(aux[1])
        return resultado

if __name__ == '__main__':
    bd = BD()
    print bd.campos_join("ALUMNOS", "CURSOS")
    